<?php

namespace ly;

use PDO;
use PDOException;

class DB {
	/**
	 * @var $this
	 */
	private static $INSTANCE; // 实例化静态对象

	private $_conf = [
		'host'   => 'localhost',
		'port'   => 3306,
		'user'   => 'root',
		'pass'   => 'root',
		'dbName' => 'admin'
	];

	/**
	 * @var PDO
	 */
	private $_dbh;
	private $_pconnect = false; // 是否使用长连接
	private $_dbType = 'mysql';
	private $_left = '`';          // 数据库转义符-头
	private $_right = '`';         // 数据库转义符-尾

	private $_tbName = '';                // 表名
	private $_alias = '';                 // 别名
	private $_mode = PDO::FETCH_ASSOC;    // 查询模式
	private $_e = null;
	private $_sql = '';                // 最后一条sql语句
	private $_cte = '';
	private $_field = '*';
	private $_where = '';
	private $_join = '';
	private $_group = '';
	private $_order = '';
	private $_limit = '';

	/**
	 * [__construct 初始化 禁用外部初始化，仅可通过静态方法config内部初始化]
	 *
	 * @param  array  $conf  [数据库配置]
	 */
	private function __construct ($conf) {
		$this->_conf = array_merge($this->_conf, $conf);

		if (!class_exists('PDO'))
			exit("未开启PDO扩展！");
	}

	// 连接数据库
	private function connect () {
		$dsn     = "{$this->_dbType}:host={$this->_conf['host']};port={$this->_conf['port']};dbname={$this->_conf['dbName']}";
		$options = $this->_pconnect ? [
			PDO::ATTR_PERSISTENT               => true,
			PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
		] : [];
		try {
			$dbh = new PDO($dsn, $this->_conf['user'], $this->_conf['pass'], $options);
			$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
		} catch (PDOException $e) {
			switch ($e->getCode()) {
				case 2002:
					exit('连接数据库失败，数据库地址填写错误！');
				case 1045:
					exit('连接数据库失败，数据库用户名或密码填写错误！');
				case 1049:
					exit('连接数据库失败，数据表不存在！');
				default:
					exit('连接数据库失败，[' . $e->getCode() . ']' . $e->getMessage());
			}
		}
		$dbh->exec('SET NAMES utf8');

		$this->_dbh = $dbh;
	}

	/**
	 * [config 静态初始化，只需执行一次]
	 *
	 * @param  array  $conf  [数据库配置]
	 *
	 * @return $this
	 */
	public static function config ($conf) {
		self::$INSTANCE = new self($conf);

		return self::$INSTANCE;
	}

	// 获取静态实例化对象

	/**
	 * @return $this
	 */
	private static function instance () {
		if (!(self::$INSTANCE instanceof self))
			exit('尚未配置数据库');

		if (is_null(self::$INSTANCE->_dbh))
			self::$INSTANCE->connect();

		return self::$INSTANCE;
	}

	/**
	 * [query 执行原生sql语句]
	 *
	 * @param  string   $sql  [sql语句]
	 * @param  boolean  $all  [是否查询多条数据]
	 *
	 * @return array|false
	 */
	public static function query ($sql, $all = true) {
		$db = self::instance();
		$db->_clear();

		return $db->_doQuery($sql, $all);
	}

	/**
	 * [exec 执行原生sql语句]
	 *
	 * @param  string  $sql  [sql语句]
	 *
	 * @return false|int
	 */
	public static function exec ($sql) {
		$db = self::instance();
		$db->_clear();

		return $db->_doExec($sql);
	}

	/**
	 * @param  string  $sql
	 *
	 * @return \ly\Raw
	 */
	public static function raw ($sql) {
		return new Raw($sql);
	}

	/**
	 *
	 * 查询数据模式
	 *
	 * @param  int  $mode  [查询数据模式]
	 *
	 * @return $this
	 */
	public function mode ($mode = PDO::FETCH_ASSOC) {
		$this->_mode = $mode;

		return $this;
	}

	/**
	 * [cte mysql8的新方法]
	 *
	 * @param  string  $cte
	 *
	 * @return $this
	 */
	public function cte ($cte) {
		$this->_cte = trim($cte);

		return $this;
	}

	/**
	 * [table 设置主表名]
	 *
	 * @param  string  $table  [表名称]
	 * @param  string  $alias  [别名]
	 *
	 * @return $this
	 */
	public static function table ($table, $alias = '') {
		$db = self::instance();
		$db->_clear();
		$db->_tbName = trim($table);
		$db->_alias  = trim($alias);

		return $db;
	}

	/**
	 * [field 查询字段]
	 *
	 * @param  string  ...$field  [查询字段]
	 *
	 * @return $this
	 */
	public function field (...$field) {
		if (empty($field)) {
			$this->_field = '*';
		} else {
			$nField       = array_map([$this, '_addChar'], $field);
			$this->_field = trim(implode(', ', $nField));
		}

		return $this;
	}

	/**
	 * AND (* AND *)
	 *
	 * @param  array|string  ...$args  [字段，[方法，[值]]] or [数组]
	 *
	 * @return $this
	 */
	public function where (...$args) {
		$this->fmt_where('AND', 'AND', ...$args);

		return $this;
	}

	/**
	 * AND (* OR *)
	 *
	 * @param  array|string  ...$args  [字段，[方法，[值]]] or [数组]
	 *
	 * @return $this
	 */
	public function whereAndOr (...$args) {
		$this->fmt_where('AND', 'OR', ...$args);

		return $this;
	}

	/**
	 * OR (* AND *)
	 *
	 * @param  array|string  ...$args  [字段，[方法，[值]]] or [数组]
	 *
	 * @return $this
	 */
	public function whereOrAnd (...$args) {
		$this->fmt_where('OR', 'AND', ...$args);

		return $this;
	}

	/**
	 * OR (* OR *)
	 *
	 * @param  array|string  ...$args  [字段，[方法，[值]]] or [数组]
	 *
	 * @return $this
	 */
	public function whereOr (...$args) {
		$this->fmt_where('OR', 'OR', ...$args);

		return $this;
	}

	/**
	 * 格式化拼接where语句
	 *
	 * @param  string        $separator  组外连接符
	 * @param  string        $delimiter  组内连接符
	 * @param  array|string  ...$args
	 */
	private function fmt_where ($separator = 'AND', $delimiter = 'AND', ...$args) {
		$wheres = [];
		if (is_array($args[0])) {
			$len = count($args);
			foreach ($args[0] as $k => $v) {
				if (is_string($k)) {
					if ($where = $this->fmt_field($k, $v))
						$wheres[] = $where;
				} else if (is_array($v)) {
					foreach ($v as $a) {
						if ($where = $this->fmt_field(...$a))
							$wheres[] = $where;
					}
				} else if ($len === 2 && ($where = $this->fmt_field($v, $args[1])))
					$wheres[] = $where;
				else if ($len === 3 && ($where = $this->fmt_field($v, $args[1], $args[2])))
					$wheres[] = $where;
			}
		} else if ($where = $this->fmt_field(...$args))
			$wheres[] = $where;

		if (!empty($wheres)) {
			$where = implode(" {$delimiter} ", $wheres);
			if ($this->_where !== '') $this->_where .= " {$separator} ";
			$this->_where .= count($wheres) > 1 || (count($args) === 1 && is_string($args[0])) ? '(' . $where . ')' : $where;
		}
	}

	/**
	 * 格式化拼接字段
	 *
	 * @param  string  ...$args
	 *
	 * @return string
	 */
	private function fmt_field (...$args) {
		switch (count($args)) {
			case 1:
				return $args[0] === null || trim($args[0]) === '' ? '' : trim($args[0]);
			case 2:
				$args[0] = $this->_addChar($args[0]);
				if ($args[1] instanceof Raw) ;
				else if ($args[1] === null)
					$args[1] = 'IS NULL';
				else if (!is_int($args[1]) && !is_float($args[1]) && !is_bool($args[1]))
					$args[1] = "LIKE '" . addslashes($args[1]) . "'";
				else
					$args[1] = "= {$args[1]}";

				return implode(' ', $args);
			case 3:
				$args[0] = $this->_addChar($args[0]);
				if ($args[2] instanceof Raw) ;
				else if ($args[2] === null) {
					$args[1] = 'IS';
					$args[2] = 'NULL';
				} else if (preg_match('/^\s*(BETWEEN)\s*$/i', $args[1])) {
					if (is_array($args[2])) $args[2] = $args[2][0] . ' AND ' . $args[2][1];
				} else if (preg_match('/^\s*(IN)\s*$/i', $args[1])) {
					if (is_array($args[2])) $args[2] = "('" . implode("', '", $args[2]) . "')";
				} else if (!is_int($args[2]) && !is_float($args[2]) && !is_bool($args[2])) {
					$args[2] = "'" . addslashes($args[2]) . "'";
				}

				return implode(' ', $args);
			default:
				return '';
		}
	}

	/**
	 * GROUP BY 分组查询
	 *
	 * @param  string  ...$args
	 *
	 * @return $this
	 */
	public function groupBy (...$args) {
		$groups = [];
		foreach ($args as $arg) {
			if (is_array($arg)) {
				foreach ($arg as $v)
					if (!empty($v && is_string($v)))
						$groups[] = $this->_addChar($v);
			} else if (!empty($arg) && is_string($arg))
				$groups[] = $this->_addChar($arg);
		}
		if (!empty($groups))
			$this->_group = implode(', ', $groups);

		return $this;
	}

	/**
	 * [order 排序规则]
	 *
	 * @param  array|string  $order  [排序规则或排序字段]
	 * @param  string|null   $type
	 *
	 * @return $this
	 */
	public function order ($order, $type = null) {
		if (null !== $type) {
			$this->_order = $this->_addChar($order) . ' ' . strtoupper($type);
		} else if (is_string($order)) {
			$this->_order = $order;
		} else if (is_array($order)) {
			$orders = [];
			foreach ($order as $k => $v) {
				if (is_string($k)) {
					$orders[] = $this->_addChar($k) . ' ' . strtoupper($v);
				} else {
					$orders[] = $v;
				}
			}
			$this->_order = implode(', ', $orders);
		}

		return $this;
	}

	/**
	 * [limit 分页查询]
	 *
	 * @param  int       $page  [页码或大小]
	 * @param  int|null  $size  [分页大小]
	 *
	 * @return $this
	 */
	public function limit ($page, $size = null) {
		if (is_numeric($size) && $size > 0)
			$this->_limit = intval(($page - 1) * $size) . ", " . $size;
		else
			$this->_limit = $page > 0 ? $page : 1;

		return $this;
	}

	/**
	 * [left 左连接]
	 *
	 * @param  string  ...$args  [表及条件]
	 *
	 * @return $this
	 */
	public function left (...$args) {
		$this->fmt_join('LEFT', ...$args);

		return $this;
	}

	/**
	 * [right 右连接]
	 *
	 * @param  string  ...$args  [表及条件]
	 *
	 * @return $this
	 */
	public function right (...$args) {
		$this->fmt_join('RIGHT', ...$args);

		return $this;
	}

	/**
	 * [inner 内连接]
	 *
	 * @param  string  ...$args  [表及条件]
	 *
	 * @return $this
	 */
	public function inner (...$args) {
		$this->fmt_join('INNER', ...$args);

		return $this;
	}

	/**
	 * 格式化拼接join
	 *
	 * @param  string  $type
	 * @param  string  ...$args
	 */
	private function fmt_join ($type = 'LEFT', ...$args) {
		if (is_array($args[0])) {
			$joins = [];
			foreach ($args[0] as $k => $v) {
				if (is_string($k)) {
					if ($join = $this->fmt_join_on($k, $v))
						$joins[] = $type . ' JOIN ' . $join;
				} else if (is_array($v)) {
					if ($join = $this->fmt_join_on(...$v))
						$joins[] = $type . ' JOIN ' . $join;
				} else if ($join = $this->fmt_join_on($v))
					$joins[] = $type . ' JOIN ' . $join;
			}
		} else if ($join = $this->fmt_join_on(...$args))
			$joins[] = $type . ' JOIN ' . $join;

		if (!empty($joins))
			$this->_join .= ($this->_join === '' ? '' : "\n") . implode("\n", $joins);
	}

	private function fmt_join_on (...$args) {
		switch (count($args)) {
			case 1:
				return trim($args[0]) === '' ? '' : trim($args[0]);
			case 2:
				if (is_array($args[1]))
					$args[1] = $this->fmt_field(...$args[1]);

				return trim($args[0]) . ' ON ' . trim($args[1]);
			case 3:
				if (is_array($args[2]))
					$args[2] = $this->fmt_field(...$args[2]);

				return $this->_addChar($args[0]) . ' AS ' . trim($args[1]) . ' ON ' . trim($args[2]);
			default:
				return '';
		}
	}

	/**
	 * [find 查询单条数据]
	 *
	 * @return array|false
	 */
	public function find () {
		return $this->select(false);
	}

	/**
	 * [select 查询数据]
	 *
	 * @param  boolean  $all  [是否查询多条数据]
	 *
	 * @return array|false
	 */
	public function select ($all = true) {
		$sqls = [
			'SELECT ' . $this->_field,
			'FROM ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
		];

		if ('' !== $this->_cte)
			array_unshift($sqls, $this->_cte);

		if ('' !== $this->_join)
			$sqls[] = $this->_join;

		if ('' !== $this->_where)
			$sqls[] = 'WHERE ' . $this->_where;

		if ('' !== $this->_group)
			$sqls[] = 'GROUP BY ' . $this->_group;

		if ('' !== $this->_order)
			$sqls[] = 'ORDER BY ' . $this->_order;

		if ('' !== $this->_limit)
			$sqls[] = 'LIMIT ' . $this->_limit;

		$sql = implode("\n", $sqls);

		return $this->_doQuery($sql, $all);
	}

	/**
	 * [update 更新数据]
	 *
	 * @param  array  $data  [更新的数据]
	 *
	 * @return false|int
	 */
	public function update ($data) {
		//安全考虑,阻止全表更新
		if ('' === $this->_where)
			return false;

		$sqls = [
			'UPDATE ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
		];

		$tbInfo = $this->_tbInfo($this->_tbName);
		$data   = $this->_dataFormat($tbInfo, $data);
		$valArr = [];
		foreach ($data as $k => $v)
			$valArr[] = $k . ' = ' . $v;
		$sqls[] = 'SET ' . implode(', ', $valArr);

		if ('' !== $this->_where)
			$sqls[] = 'WHERE ' . $this->_where;

		$sql = implode("\n", $sqls);

		return $this->_doExec($sql);
	}

	/**
	 * [insert 插入数据]
	 *
	 * @param  array  $data   [数据]
	 * @param  bool   $batch  [是否批量模式]
	 *
	 * @return false|int
	 */
	public function insert ($data, $batch = false) {
		$tbInfo = $this->_tbInfo($this->_tbName);
		if ($batch) {
			$keys = $values = [];
			foreach ($data as $a) {
				$data2 = $this->_dataFormat($tbInfo, $a);
				if (empty($keys)) $keys = array_keys($data2);
				$values[] = '(' . implode(', ', array_values($data2)) . ')';
			}
			$sqls = [
				'INSERT INTO ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
				'(' . implode(', ', $keys) . ') VALUES',
				implode(",\n", $values)
			];
		} else {
			$data = $this->_dataFormat($tbInfo, $data);
			$sqls = [
				'INSERT INTO ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
				'(' . implode(', ', array_keys($data)) . ') VALUES',
				'(' . implode(', ', array_values($data)) . ')',
			];
		}

		$sql = implode("\n", $sqls);

		return $this->_doExec($sql);
	}

	/**
	 * [replace 更新或插入数据]
	 *
	 * @param  array  $data   [数据]
	 * @param  bool   $batch  [是否批量模式]
	 *
	 * @return false|int
	 */
	public function replace ($data, $batch = false) {
		$tbInfo = $this->_tbInfo($this->_tbName);
		if ($batch) {
			$keys = $values = [];
			foreach ($data as $a) {
				$data2 = $this->_dataFormat($tbInfo, $a);
				if (empty($keys)) $keys = array_keys($data2);
				$values[] = '(' . implode(', ', array_values($data2)) . ')';
			}
			$sqls = [
				'REPLACE INTO ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
				'(' . implode(', ', $keys) . ') VALUES',
				implode(",\n", $values)
			];
		} else {
			$data = $this->_dataFormat($tbInfo, $data);
			$sqls = [
				'REPLACE INTO ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
				'(' . implode(', ', array_keys($data)) . ') VALUES',
				'(' . implode(', ', array_values($data)) . ')',
			];
		}

		$sql = implode("\n", $sqls);

		return $this->_doExec($sql);
	}

	/**
	 * [delete 删除数据]
	 *
	 * @return false|int
	 */
	public function delete () {
		//安全考虑,阻止全表删除
		if ('' === $this->_where)
			return false;

		$sqls = [
			'DELETE FROM ' . $this->_addChar($this->_tbName) . ($this->_alias === '' ? '' : ' AS ' . $this->_alias),
		];
		if ('' != $this->_where)
			$sqls[] = 'WHERE ' . $this->_where;

		$sql = implode("\n", $sqls);

		return $this->_doExec($sql);
	}

	/**
	 * 清空数据表
	 *
	 * @return false|int
	 */
	public function truncate () {
		$sql = "TRUNCATE TABLE " . $this->_addChar($this->_tbName);

		return $this->_doExec($sql);
	}

	/**
	 * [count 计算总数据条数]
	 *
	 * @return int
	 */
	public static function count () {
		$db = self::instance();

		$sqls = [
			"SELECT COUNT(*) AS {$db->_left}count{$db->_right}",
			'FROM ' . $db->_addChar($db->_tbName),
		];

		if ('' !== $db->_cte)
			array_unshift($sqls, $db->_cte);

		if ('' !== $db->_join)
			$sqls[] = $db->_join;

		if ('' !== $db->_where)
			$sqls[] = 'WHERE ' . $db->_where;

		$sql = implode("\n", $sqls);

		return ($res = $db->_doQuery($sql, false)) ? $res['count'] : 0;
	}

	public static function clear () {
		$db = self::instance();
		$db->_clear();

		return $db;
	}

	/**
	 * [last_insert_id 获取当前最后插入数据的自增值]
	 *
	 * @return int|null
	 */
	public static function last_insert_id () {
		$db = self::instance();

		$sql = "SELECT LAST_INSERT_ID() AS `id`";

		return ($res = $db->_doQuery($sql, false)) && $res['id'] > 0 ? $res['id'] : null;
	}

	/**
	 * 设置自增值
	 */
	public function increment ($number = 1) {
		return $this->_doQuery("ALTER TABLE " . $this->_addChar($this->_tbName) . " AUTO_INCREMENT = {$number}");
	}

	/**
	 * [last_sql 获取执行的最后一条sql语句]
	 *
	 * @return string
	 */
	public static function last_sql () {
		return self::instance()->_sql;
	}

	/**
	 * [last_error 获取最后一次异常]
	 *
	 * @return \PDOException|null
	 */
	public static function last_error () {
		return self::instance()->_e;
	}

	/**
	 * 获取数据库版本
	 *
	 * @return false|string
	 */
	public static function version () {
		$db = self::instance();

		$row = $db->_doQuery("SELECT VERSION() AS " . $db->_addChar('version'), false);

		return $row['version'];
	}

	/**
	 * 字段和表名添加 `符号
	 * 保证指令中使用关键字不出错
	 *
	 * @param  string  $value
	 *
	 * @return string
	 */
	private function _addChar ($value) {
		return preg_match('/^\w+$/', trim($value)) ? $this->_left . trim($value) . $this->_right : trim($value);
	}

	/**
	 * 执行查询 主要针对 SELECT, SHOW 等指令
	 *
	 * @param  string   $sql  sql指令
	 * @param  boolean  $all  是否查询多条数据
	 *
	 * @return array|false
	 */
	private function _doQuery ($sql = '', $all = true) {
		$this->_sql = $sql;

		try {
			$stmt = $this->_dbh->prepare($sql); //prepare或者query 返回一个PDOStatement
			$stmt->execute();

			return $all ? $stmt->fetchAll($this->_mode) : $stmt->fetch($this->_mode);
		} catch (PDOException $e) {
			$this->_e = $e;

			return false;
		}
	}

	/**
	 * 执行语句 针对 INSERT, UPDATE 以及DELETE,exec结果返回受影响的行数
	 *
	 * @param  string  $sql  sql指令
	 *
	 * @return false|int
	 */
	private function _doExec ($sql = '') {
		$this->_sql = $sql;

		try {
			$this->_dbh->beginTransaction();
			$ret = $this->_dbh->exec($sql);
			$this->_dbh->commit();

			return $ret;
		} catch (PDOException $e) {
			$this->_e = $e;
			$this->_dbh->rollBack();

			return false;
		}
	}

	/**
	 * 清理标记函数
	 */
	private function _clear () {
		$this->_mode   = PDO::FETCH_ASSOC;
		$this->_e      = null;
		$this->_tbName = '';
		$this->_alias  = '';
		$this->_cte    = '';
		$this->_where  = '';
		$this->_group  = '';
		$this->_order  = '';
		$this->_limit  = '';
		$this->_field  = '*';
		$this->_join   = '';
	}

	/**
	 * 取得数据表的相关信息
	 *
	 * @param  string  $tbName
	 *
	 * @return array
	 */
	private function _tbInfo ($tbName) {
		$sql = 'SELECT COLUMN_NAME AS `name`, DATA_TYPE AS `type`, IS_NULLABLE AS `nullable` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "' . $tbName . '"';

		$stmt = $this->_dbh->prepare($sql);
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

		$names = array_column($result, 'name');

		return array_combine($names, $result);
	}

	/**
	 * 过滤并格式化数据表字段
	 *
	 * @param  array  $tbInfo  [表字段信息]
	 * @param  array  $data    [数据]
	 *
	 * @return array
	 */
	private function _dataFormat ($tbInfo, $data) {
		$arr = [];
		foreach ($data as $key => $val) {
			if (($val !== null && !is_scalar($val) && !($val instanceof Raw)) || !array_key_exists($key, $tbInfo))
				continue; //值不是标量或字段不存在则跳过

			$info = $tbInfo[$key];
			$type = strtolower($info['type']);
			$key  = $this->_addChar($key);
			if ($val instanceof Raw) ;
			else if (($val === '' || $val === null) && $info['nullable'] === 'YES')
				$val = 'NULL';
			else if ($type == 'bit')
				$val = var_export(boolval($val), true);
			else if (in_array($type, [
				'int', 'tinyint', 'smallint', 'mediumint', 'numeric', 'integer', 'bigint', 'timestamp'
			]))
				$val = is_numeric($val) ? intval($val) : $val;
			else if ('double' === $type)
				$val = doubleval($val);
			else if ('float' === $type || 'decimal' === $type)
				$val = floatval($val);
			else if (preg_match('/^\s*(\(.+\)|\w+\(\))\s*$/s', $val))
				// 支持简单表达式,例如 (score+1) NOW() 必须包含括号 弃用，请使用DB::raw
				$val = $val;
			else if (is_string($val))
				$val = "'" . addslashes($val) . "'";

			$arr[$key] = $val;
		}

		return $arr;
	}
}

class Raw {
	/**
	 * 查询表达式
	 *
	 * @var string
	 */
	protected $value;

	/**
	 * 创建一个查询表达式
	 *
	 * @param  string  $value
	 *
	 * @return void
	 */
	public function __construct ($value) {
		$this->value = $value;
	}

	/**
	 * 获取表达式
	 *
	 * @return string
	 */
	public function getValue () {
		return $this->value;
	}

	public function __toString () {
		return (string)$this->value;
	}
}